﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Collections;
using System.Collections.ObjectModel;

using System.Data;
using System.Data.SqlClient;
using TimeUpdate.Help;
using System.Configuration;
using LogHelp;

namespace TimeUpdate.Access
{
    class TimeUpdatAccess
    {
        SqlDataLayer sqlHelper = new SqlDataLayer();
        Log log = new Log();
        private int line;

        public TimeUpdatAccess(int line)
        {
            this.line = line;
        }
        public TimeUpdatAccess()
        {
        }
        /// <summary>
        /// 初始化参数
        /// </summary>
        public void GetConfig(ref Dictionary<string, string> config)
        {
            try
            {
                string sql_getconfig = "select * from ConfigSetting where cfg_view = 'DriverTeamView' and cfg_line = " + line;
                DataTable configDT = sqlHelper.SelectDt(sql_getconfig);
                if (null != configDT && configDT.Rows.Count > 0)
                {
                    for (int i = 0; i < configDT.Rows.Count; i++)
                    {
                        string key = configDT.Rows[i]["cfg_key"].ToString();
                        string value = configDT.Rows[i]["cfg_value"].ToString();

                        config.Add(key, value);
                    }

                }
            }
            catch (Exception ex)
            {
                log.writeError(ex.ToString());
            }
        }

        /// <summary>
        /// 返回早、白、夜、休 分别对应的司机队伍
        /// </summary>
        /// <returns></returns>
        public DataTable GetGroupToTeam()
        {
            string sql_get = "select ctn_cls_id as zbyx ,ctn_team as team from ClassTurn where line = " + line;
            return sqlHelper.SelectDt(sql_get);
        }

        /// <summary>
        /// 选出早班，白班，夜班，的司机
        /// </summary>
        /// <returns></returns>
        public DataTable GetDriverInfoByClass()
        {
            try
            {
                DataTable DriverDT = null;

                string sql_selectDriver = "select S.user_name," +
                                        " S.user_view_name," +
                                        " user_pn," +
                                        " Ctn.ctn_cls_id as cls_id," +
                                        " Cr.reptrain ," +
                                        " Cr.attenPlace, " +
                                        " Cr.attenTime as planAtten," +
                                        " Dg.dgrp_attendtime as actualAtten," +
                                        " Dg.dgrp_outtime as outtime," +
                                        " Dcom.dcom_grp_type as Gtype" +
                                        " from " +
                                        " DriverGroup Dg left join [system_user] S on Dg.dgrp_user_id = S.user_id" +
                                        " left join DriverCombine Dcom on Dg.dgrp_dcom_id = Dcom.ID" +
                                        " left join ClassTurn Ctn on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
                                        " left join CrossingRoad Cr on Dcom.dcom_cr_number = Cr.ID" +
                                        " where  Ctn.ctn_cls_id <> 1 and Ctn.line = " + line + " order by cls_id,planAtten";

                DriverDT = sqlHelper.SelectDt(sql_selectDriver);
                return DriverDT;
            }
            catch (Exception ex)
            {
                log.writeError(ex.ToString());
                return null;
            }
        }

        public DataTable GetDriverInfo()
        {
            try
            {
                DataTable DriverDT = null;

                //以什么排序可能要调整一下，根据出勤时间？
                string SQL_selectDriver = "select D.dgrp_user_id as user_id,S.user_name as jobNum ,S.user_view_name as user_name,C.ctn_cls_id as cls_id," +
                                        " D.dgrp_srd_id as srd_id," +
                                        " Dr.dcom_dutynum as duty_num,Cr.number as cr_num, Dr.dcom_grp_type as grp_type,S.user_line as line" +
                                        " from DriverGroup D left join [system_user] S on D.dgrp_user_id = S.user_id" +
                                        " left join DriverCombine Dr on D.dgrp_dcom_id = Dr.ID" +
                                        " left join CrossingRoad Cr on Dr.dcom_cr_number = Cr.ID" +
                                        " left join ClassTurn C on C.ctn_team = Dr.dcom_team and C.line = D.dgrp_line" +
                                        " where D.dgrp_line = " + line + " order by Dr.dcom_dutynum";

                DriverDT = sqlHelper.SelectDt(SQL_selectDriver);

                return DriverDT;
            }
            catch (Exception ex)
            {
                log.writeError(ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 检查今天是否执行过班组轮换，是的话返回true,否返回false
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool CheckHasTurn()
        {
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select count(*) from ReTurnRecord where re_date = '" + today + "' and re_line =" + line;

            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }

        /// <summary>
        /// 执行班组轮换，成功后，在ReTrunGroup中插入相应记录
        /// </summary>
        /// <returns></returns>
        public bool ExeTurnGroups(ref string error)
        {
            ArrayList sqlList = new ArrayList();

            ///班组轮换
            string sql_exeTurn = "update ClassTurn set ctn_cls_id = (ctn_cls_id + 1)%4 where line = " + line;

            ///更新DriverGroup里面的司机出勤信息或调班信息,将dgrp_date改为今天
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_setNull = "update DriverGroup set dgrp_dcom_id = null,dgrp_srd_id = null,dgrp_attendtime = null,dgrp_outtime = null,dgrp_room = null,dgrp_date='" + today + "' where dgrp_line = " + line;

            sqlList.Add(sql_exeTurn);
            sqlList.Add(sql_setNull);

            ///将DriverCombine对应的信息赋予相应司机
            string sql_update = "select D.ID as DgID ,D.dgrp_user_id ,Dcom.ID as DcomID" +
                                " from DriverGroup D left join DriverCombine Dcom on " +
                                " D.dgrp_user_id = Dcom.dcom_opdriver or D.dgrp_user_id = Dcom.dcom_monidriver" +
                                " where Dcom.ID is not null and D.dgrp_line = " + line;

            DataTable Dt = sqlHelper.SelectDt(sql_update);
            if (null == Dt || Dt.Rows.Count < 0)
                return false;

            for (int i = 0; i < Dt.Rows.Count; i++)
            {
                string sql = "update DriverGroup set dgrp_dcom_id = '" + Dt.Rows[i]["DcomID"].ToString() + "' where ID = '" + Dt.Rows[i]["DgID"].ToString() + "'";
                sqlList.Add(sql);
            }

            if (!sqlHelper.ExecuteSqlTran(sqlList))
            {
                error = "执行班组轮换失败！";
                return false;
            }

            //班组轮换成功后再调整司机的调班信息
            error = AddSwitchRecord(today);

            return string.IsNullOrEmpty(error);
        }

        /// <summary>
        /// 添加调班信息到DriverGroup表
        /// </summary>
        /// <returns></returns>
        public string AddSwitchRecord(string today)
        {
            ArrayList sqlList = new ArrayList();
            //检查今天的调班信息
            string sql_selectRecord = "select * from SwitchedRecord where sr_appdate > '" + today + "' or sr_chgdate > '" + today + "'";

            DataTable chgDT = sqlHelper.SelectDt(sql_selectRecord);
            if (chgDT != null && chgDT.Rows.Count > 0)
            {
                try
                {
                    for (int i = 0; i < chgDT.Rows.Count; i++)
                    {
                        int recordID = Convert.ToInt32(chgDT.Rows[i]["ID"]);
                        int app = Convert.ToInt32(chgDT.Rows[i]["sr_applicant"]);
                        int chg = Convert.ToInt32(chgDT.Rows[i]["sr_changer"]);
                        string appdate = Convert.ToString(chgDT.Rows[i]["sr_appdate"]);  //调换前日期
                        string chgdate = Convert.ToString(chgDT.Rows[i]["sr_chgdate"]); //调换后日期
                        string update1 = null;
                        string update2 = null;
                        if (today == appdate && appdate == chgdate)   //调班与还班是同一天
                        {
                            string app_dcomId = "";
                            string chg_dcomId = "";
                            string sql_getapp_dcomid = "select dgrp_dcom_id from DriverGroup where dgrp_user_id = " + app;
                            string sql_getchg_dcomid = "select dgrp_dcom_id from DriverGroup where dgrp_user_id = " + chg;
                            try
                            {
                                app_dcomId = Convert.ToString(sqlHelper.GetSingle(sql_getapp_dcomid));
                                chg_dcomId = Convert.ToString(sqlHelper.GetSingle(sql_getchg_dcomid));
                                if (string.IsNullOrEmpty(app_dcomId) && string.IsNullOrEmpty(chgdate))
                                    continue;
                                update1 = "update DriverGroup set dgrp_srd_id = " + recordID + ", dgrp_dcom_id = '" + chg_dcomId + "' where dgrp_user_id = " + app;
                                update2 = "update DriverGroup set dgrp_srd_id = " + recordID + ", dgrp_dcom_id = '" + app_dcomId + "' where dgrp_user_id = " + chg;
                            }
                            catch (System.Exception ex)
                            {

                            }
                        }
                        else if (today == appdate) //调班日期，申请者不上班，调换人上班
                        {
                            update1 = "update DriverGroup set dgrp_srd_id = " + recordID +
                                ",dgrp_dcom_id = (select dgrp_dcom_id from DriverGroup where dgrp_user_id = " +
                                app + ") where dgrp_user_id = " + chg;  //调换人上班
                            update2 = "update DriverGroup set dgrp_srd_id = " + recordID +
                                ",dgrp_dcom_id = null where dgrp_user_id = " + app; //申请人不上班
                        }
                        else if (today == chgdate) //还班日期  申请人上班，调换人不上班
                        {
                            update1 = "update DriverGroup set dgrp_srd_id = " + recordID +
                                ",dgrp_dcom_id =(select dgrp_dcom_id from DriverGroup where dgrp_user_id = " +
                                chg + ") where dgrp_user_id = " + app;
                            update2 = "update DriverGroup set dgrp_srd_id = " + recordID +
                                ",dgrp_dcom_id = null where dgrp_user_id = " + chg;
                        }
                        if (!string.IsNullOrEmpty(update1) && !string.IsNullOrEmpty(update2))
                        {
                            sqlList.Add(update1);
                            sqlList.Add(update2);
                        }
                    }
                    sqlHelper.ExecuteSqlTran(sqlList);
                }
                catch (System.Exception ex)
                {
                    string err = "调整调班信息时出现异常：" + ex.Message;
                    return err;
                }
            }
            return null;
        }

        public bool AddTrunGroupsRecord()
        {

            try
            {
                string sql_add = "insert into ReTurnRecord values('" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + line + "')";
                if (sqlHelper.ExecuteSql(sql_add) > 0)
                {
                    log.writeInfo("已经写入每日班组轮换记录");

                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                log.writeError(ex.ToString());
                return false;
            }
        }
        public void DeleteTempAttend()
        {
            try
            {
                sqlHelper.ExecuteSql("delete t2 from DriverGroupOutTime t1,drivergroup t2 where t1.DGID=t2.id and  t1.istemp=1 and t2.dgrp_line="+line+";delete t1 from DriverGroupOutTime t1 where t1.istemp = 1 and t1.dgid not in (select id from DriverGroup)");
                log.writeInfo("已经删除临时出勤记录");
            }catch(Exception ex)
            {
                log.writeError(ex.ToString());
                return;
            }
        }
        /// <summary>
        /// 更新司机长，派班员，督导员的班次
        /// </summary>
        public void UpdateSpecial()
        {

        }
        /// <summary>
        /// 将昨日的出勤记录添加到 DriverAttendRecord
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool AddAttenRecord()
        {
            bool result = true;
            string sql_selelctRecord = "select" +
                                        " Dr.dgrp_user_id," +
                                        " [Post].post_name," +
                                        " Dr.dgrp_date," +
                                        " Dr.dgrp_line," +
                                        " Ctn.ctn_cls_id," +
                                        " Dcom.dcom_dutynum," +
                                        " Cr.ID as cr_ID," +
                                        " Dr.dgrp_attendtime," +
                                        " Dr.dgrp_outtime," +
                                        " Dcom.dcom_grp_type," +
                                        " Dr.dgrp_room," +
                                        "Dr.replaceDriver,"+
                                        "Dr.dgrp_pbyInfo," + "Dcom.dcom_team," +
                                        " dr.attentime," + "Dr.dgrp_dtq" +
                                        " from DriverGroup Dr left join DriverCombine Dcom on (Dr.dgrp_dcom_id = Dcom.ID and Dr.dgrp_line = Dcom.dcom_lineid)" +
                                        " left join [system_user] on Dr.dgrp_user_id = user_id" +
                                        " left join Position [Post] on [Post].ID = user_post" +
                                        " left join ClassTurn Ctn on (Ctn.ctn_cls_id in (0,2,3) and Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)" +
                                        " left join CrossingRoad Cr on  (Dcom.dcom_cr_number = Cr.ID)" +
                                        " where Dr.dgrp_line = " + line;
            try
            {
                DataTable resultDT = sqlHelper.SelectDt(sql_selelctRecord);
                SqlBulkCopy bcp = new SqlBulkCopy(SqlDataLayer.ConnStr, SqlBulkCopyOptions.KeepIdentity);
                bcp.DestinationTableName = "DriverAttendRecord";
                bcp.BatchSize = 100;
                for (int i = 0; i < resultDT.Columns.Count; i++)
                {
                    bcp.ColumnMappings.Add(i, i + 1);
                }
                bcp.WriteToServer(resultDT);
            }
            catch (System.Exception ex)
            {
                log.writeError(ex.ToString());
                result = false;

            }
            log.writeInfo("已经将昨日的出勤记录添加到 DriverAttendRecord");
            return result;
        }

        /// <summary>
        /// 获取上一次执行编号循环的日期
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public string GetLastNumCycleDay()
        {
            string sql_getlastdate = "select top 1 re_date from ReNumRecord where re_line = " + line + " order by re_date desc";
            return Convert.ToString(sqlHelper.GetSingle(sql_getlastdate));
        }
        /// <summary>
        /// 执行编号循环
        /// </summary>
        /// <param name="line"></param>
        /// <param name="isTurnBackup">指示是否轮换备用司机</param>
        /// <returns></returns>
        public bool ExeNumCycle()
        {
            ArrayList sqlList = new ArrayList();

            int maxT1 = Convert.ToInt32(sqlHelper.GetSingle("select max(dcom_dutynum) from DriverCombine where dcom_lineid='" + line + "' and dcom_team= 1"));
            int maxT2 = Convert.ToInt32(sqlHelper.GetSingle("select max(dcom_dutynum) from DriverCombine where dcom_lineid='" + line + "' and dcom_team= 2"));
            int maxT3 = Convert.ToInt32(sqlHelper.GetSingle("select max(dcom_dutynum) from DriverCombine where dcom_lineid='" + line + "' and dcom_team= 3"));
            int maxT4 = Convert.ToInt32(sqlHelper.GetSingle("select max(dcom_dutynum) from DriverCombine where dcom_lineid='" + line + "' and dcom_team= 4"));

            //首先不考虑备班司机的轮换
            //string sql_updateAll = "update DriverCombine set dcom_dutynum = dcom_dutynum + 1 where dcom_grp_type<>4 and dcom_lineid = " + line; 
            string sql_updateAll = "update DriverCombine set dcom_dutynum = dcom_dutynum + 1 where dcom_lineid = " + line; //备用司机不轮换
            string sql_updateT1 = "update DriverCombine set dcom_dutynum = dcom_dutynum%" + maxT1 + " where dcom_team = 1 and dcom_dutynum <>" + maxT1 + " and dcom_lineid =" + line;
            string sql_updateT2 = "update DriverCombine set dcom_dutynum = dcom_dutynum%" + maxT2 + " where dcom_team = 2 and dcom_dutynum <>" + maxT2 + " and dcom_lineid =" + line;
            string sql_updateT3 = "update DriverCombine set dcom_dutynum = dcom_dutynum%" + maxT3 + " where dcom_team = 3 and dcom_dutynum <>" + maxT3 + " and dcom_lineid =" + line;
            string sql_updateT4 = "update DriverCombine set dcom_dutynum = dcom_dutynum%" + maxT4 + " where dcom_team = 4 and dcom_dutynum <>" + maxT4 + " and dcom_lineid =" + line;

            sqlList.Add(sql_updateAll);
            sqlList.Add(sql_updateT1);
            sqlList.Add(sql_updateT2);
            sqlList.Add(sql_updateT3);
            sqlList.Add(sql_updateT4);

            //更新dutynum与备班司机相同的那组为 (dutynum + 1) % Max

            //string sql_updateSame1 = "update DriverCombine set dcom_dutynum = (dcom_dutynum + 1) %" + maxT1 +
            //" where dcom_team = 1 and dcom_grp_type <>4 and dcom_lineid = " + line + " and dcom_dutynum = ("+
            //" select dcom_dutynum from DriverCombine where dcom_lineid =" + line + " and dcom_team = 1 and dcom_grp_type = 4)";

            //string sql_updateSame2 = "update DriverCombine set dcom_dutynum = (dcom_dutynum + 1) %" + maxT2 +
            //" where dcom_team = 1 and dcom_grp_type <>4 and dcom_lineid = " + line + " and dcom_dutynum = (" +
            //" select dcom_dutynum from DriverCombine where dcom_lineid =" + line + " and dcom_team = 1 and dcom_grp_type = 4)";

            //string sql_updateSame3 = "update DriverCombine set dcom_dutynum = (dcom_dutynum + 1) %" + maxT3 +
            //" where dcom_team = 1 and dcom_grp_type <>4 and dcom_lineid = " + line + " and dcom_dutynum = (" +
            //" select dcom_dutynum from DriverCombine where dcom_lineid =" + line + " and dcom_team = 1 and dcom_grp_type = 4)";

            //string sql_updateSame4 = "update DriverCombine set dcom_dutynum = (dcom_dutynum + 1) %" + maxT4 +
            //" where dcom_team = 1 and dcom_grp_type <>4 and dcom_lineid = " + line + " and dcom_dutynum = (" +
            //" select dcom_dutynum from DriverCombine where dcom_lineid =" + line + " and dcom_team = 1 and dcom_grp_type = 4)";

            //sqlList.Add(sql_updateSame1);
            //sqlList.Add(sql_updateSame2);
            //sqlList.Add(sql_updateSame3);
            //sqlList.Add(sql_updateSame4);

            return sqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 成功执行编号循环后添加记录到 ReNumRecord表
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool AddNumCycleRecord()
        {
            string sql_add = "insert into ReNumRecord values('" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + line + "')";
            return sqlHelper.ExecuteSql(sql_add) > 0;
        }

        /// <summary>
        /// 将DriverCombine对应的信息赋予相应司机
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool UpdateDutyInfo()
        {
            string sql_update = "select D.ID as DgID ,D.dgrp_user_id ,Dcom.ID as DcomID" +
                                " from DriverGroup D left join DriverCombine Dcom on " +
                                " D.dgrp_user_id = Dcom.dcom_opdriver or D.dgrp_user_id = Dcom.dcom_monidriver" +
                                " where Dcom.ID is not null";


            return sqlHelper.ExecuteSql(sql_update) > 0;
        }

        /// <summary>
        /// 检查是否有安排时刻表到日期
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool CheckHasTimeTableToDate(ref int tableID, ref string tableName, string today)
        {
            //string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select TTd.Tableinfo_id,TIF.TableName" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                " where TIF.LineID = " + line + " and TTD.AssignDate = '" + today + "'";

            DataTable dt = sqlHelper.SelectDt(sql_check);
            if (null != dt && dt.Rows.Count > 0)
            {
                tableID = Convert.ToInt32(dt.Rows[0]["Tableinfo_id"]);
                tableName = dt.Rows[0]["TableName"].ToString();
            }

            return tableID == -1 ? false : true;
        }



        /// <summary>
        /// 检查是否有该时刻表对应的交路表信息
        /// </summary>
        /// <returns></returns>
        public bool CheckHasCrossRoad(int tableID)
        {
            string sql_check = "select count(*) from CrossingRoad where tableInfo_id = " + tableID + " and isused = 1";
            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }

        /// <summary>
        /// 今天是否已将交路表的信息刷新到DriverCombine表
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool CheckHasRefreshCrossRoad()
        {
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select count(*) from CrossRoadRecord where re_date ='" + today + "' and line = " + line;
            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }

        /// <summary>
        /// 增加今天已安排交路到司机的记录到CrossRoadRecord表
        /// </summary>
        /// <returns></returns>
        public bool AddRecordToRefreshCrossRoad()
        {
            try
            {
                string sql_add = "insert into CrossRoadRecord values('" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + line + "')";
                if (sqlHelper.ExecuteSql(sql_add) > 0)
                {
                    log.writeInfo("已经增加今天已安排交路到司机的记录到CrossRoadRecord表");
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                log.writeError(ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns></returns>
        public bool TestConnection()
        {
            if (sqlHelper.MyOpen())
            {
                sqlHelper.MyClose();
                return true;
            }
            else
            {
                sqlHelper.MyClose();
                return false;
            }
        }

        public bool IsAlreadyUpdate()
        {
            try
            {
                string sql_check = "select * from CrossRoadRecord where re_date = '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and line='" + line.ToString() + "'";
                DataTable dt = sqlHelper.SelectDt(sql_check);
               
                if(dt.Rows.Count>0)
                {
                    return true;
                }
              
                return false;
            }
            catch (Exception ex)
            {
                log.writeError(ex.ToString());
                return false;
            }
        }

        /// <summary>
        ///更新DriverCombine表中的相应的Crssroad字段，没完善。
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        //public bool RefreshCrossingRoad(int tableID,string tableName,ref string error)
        //{
        //    bool result = false;

        //    //从交路表里选合适的交路，如果以后增加了备用，热备，调车等交路，需要更改筛选条件
        //    string crossRoadZB = "select ID,number from CrossingRoad where tableInfo_id = " + tableID + " and zby = 0 and isused = 1 and grp_id = 5 order by number";
        //    string crossRoadBB = "select ID,number from CrossingRoad where tableInfo_id = " + tableID + " and zby = 2 and isused = 1 and grp_id = 5 order by number";
        //    string crossRoadYB = "select ID,number from CrossingRoad where tableInfo_id = " + tableID + " and zby = 3 and isused = 1 and grp_id = 5 order by number";

        //    DataTable roadsZB = sqlHelper.SelectDt(crossRoadZB);
        //    DataTable roadsBB = sqlHelper.SelectDt(crossRoadBB);
        //    DataTable roadsYB = sqlHelper.SelectDt(crossRoadYB);

        //    //早班司机按夜班回库顺序 dcom_ybarrive 排序
        //    string teamZB = "select Dcom.ID as DcomID,Dcom.dcom_dutynum as duytnum,Dcom.dcom_team as team"+
        //                    " from DriverCombine Dcom left join ClassTurn Ctn" + 
        //                    " on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)"+
        //                    " where Dcom.dcom_lineid = " + line + " and Ctn.ctn_cls_id = 0 and Dcom.dcom_grp_type = 5" +
        //                    " order by Dcom.dcom_ybarrive, Dcom.dcom_dutynum ";

        //    string teamBB = "select Dcom.ID as DcomID,Dcom.dcom_dutynum as duytnum,Dcom.dcom_team as team"+
        //                    " from DriverCombine Dcom left join ClassTurn Ctn" + 
        //                    " on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)"+
        //                    " where Dcom.dcom_lineid = " + line + " and Ctn.ctn_cls_id = 2 and Dcom.dcom_grp_type = 5" +
        //                    " order by Dcom.dcom_dutynum ";

        //    string teamYB = "select Dcom.ID as DcomID,Dcom.dcom_dutynum as duytnum,Dcom.dcom_team as team"+
        //                    " from DriverCombine Dcom left join ClassTurn Ctn" + 
        //                    " on (Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line)"+
        //                    " where Dcom.dcom_lineid = " + line + " and Ctn.ctn_cls_id = 3 and Dcom.dcom_grp_type = 5" +
        //                    " order by Dcom.dcom_dutynum ";

        //    DataTable teamZBDT = sqlHelper.SelectDt(teamZB);
        //    DataTable teamBBDT = sqlHelper.SelectDt(teamBB);
        //    DataTable teamYBDT = sqlHelper.SelectDt(teamYB);

        //    if (roadsZB.Rows.Count > teamZBDT.Rows.Count){
        //        string team = teamZBDT.Rows[0]["team"].ToString();
        //        error = string.Format("电客车司机 {0} 组的司机正线分组数:{1} 组，与时刻表:{2} 对应的早班正线交路数:{3} 不一致，请调整后重试！",
        //            team,teamZBDT.Rows.Count,tableName,roadsZB.Rows.Count);
        //        return result;
        //    }
        //    if (roadsBB.Rows.Count > teamBBDT.Rows.Count)
        //    {
        //        string team = teamBBDT.Rows[0]["team"].ToString();
        //        error = string.Format("电客车司机 {0} 组的司机正线分组数:{1} 组，与时刻表:{2} 对应的白班正线交路数:{3} 不一致，请调整后重试！",
        //            team, teamBBDT.Rows.Count, tableName, roadsBB.Rows.Count);
        //        return result;
        //    }
        //    if (roadsYB.Rows.Count > teamYBDT.Rows.Count)
        //    {
        //        string team = teamYBDT.Rows[0]["team"].ToString();
        //        error = string.Format("电客车司机 {0} 组的司机正线分组数:{1} 组，与时刻表:{2} 对应的夜班正线交路数:{3} 不一致，请调整后重试！",
        //            team, teamYBDT.Rows.Count, tableName, roadsYB.Rows.Count);
        //        return result;
        //    }

        //    ArrayList SqlList = new ArrayList();

        //    //首先将DriverCombine的 dcom_cr_number 字段清空
        //    string sql_clear = "update DriverCombine set dcom_cr_number = null where dcom_lineid = " + line;
        //    SqlList.Add(sql_clear);

        //    if (
        //        !( refreshSqlList(roadsZB,teamZBDT,ref SqlList) && 
        //            refreshSqlList(roadsBB, teamBBDT, ref SqlList) && 
        //            refreshSqlList(roadsYB, teamYBDT, ref SqlList))
        //        )
        //    {
        //        return result;
        //        error = "执行交路更新出现异常！更新失败！";
        //    }

        //    //热备，备用，调车等特殊交路的处理
        //    string sql_specialgroup = "select"+
        //                    " Dcom.ID as Dcom_ID,"+
        //                    " Cr.ID as Cr_ID,"+
        //                    " Cr.zby"+
        //                    " from DriverCombine Dcom left join ClassTurn Ctn on Dcom.dcom_team = Ctn.ctn_team and Dcom.dcom_lineid = Ctn.line"+
        //                    " left join CrossingRoad Cr on Dcom.dcom_grp_type = Cr.grp_id and Ctn.ctn_cls_id = Cr.zby"+
        //                    " where tableInfo_id = '" + tableID + "' and isused = 1 and Dcom.dcom_lineid = " + line + " and Dcom.dcom_grp_type <> 5";
        //    DataTable specialDT = sqlHelper.SelectDt(sql_specialgroup);
        //    AddSpecialGroup(specialDT,ref SqlList);


        //    //先将夜班的清理掉
        //    string sql_clearybarrive = "update DriverCombine set dcom_ybarrive = null";
        //    SqlList.Add(sql_clearybarrive);

        //    string ybIndex = "select Dcom.ID as Dcom_ID ," +
        //                    " Dcom.dcom_team as Dcon_team, "+
        //                    " CT.ctn_cls_id as CT_Class ," +
        //                    " Cr.number as Cr_num ," +
        //                    " Cr.offTime as Cr_offTime," +
        //                    " Cr.zby" +
        //                    " from DriverCombine Dcom " +
        //                    " left join ClassTurn CT on Dcom.dcom_team = CT.ctn_team and Dcom.dcom_lineid = CT.line" +
        //                    " left join CrossingRoad Cr on Dcom.dcom_cr_number = Cr.ID" +
        //                    " where Dcom.dcom_lineid = " + line + " and CT.ctn_cls_id = 3 order by Cr_offTime";

        //    DataTable ybArriveDT = sqlHelper.SelectDt(ybIndex);

        //    UpdateYbOffTime(ybArriveDT);

        //    UpdateYbArriverInfo(ybArriveDT,ref SqlList);

        //    result = sqlHelper.ExecuteSqlTran(SqlList);

        //    return result;
        //}

        /// <summary>
        /// 特殊班组司机的交路处理
        /// </summary>
        private void AddSpecialGroup(DataTable specialDT, ref ArrayList sqlList)
        {
            if (null == specialDT || specialDT.Rows.Count < 1)
                return;

            for (int i = 0; i < specialDT.Rows.Count; i++)
            {
                string roadID = specialDT.Rows[i]["Cr_ID"].ToString();
                string teamID = specialDT.Rows[i]["Dcom_ID"].ToString();

                string sql = "update DriverCombine set dcom_cr_number = '" + roadID + "' where ID = '" + teamID + "'";

                sqlList.Add(sql);
            }
        }

        /// <summary>
        /// 按照夜班的回库顺序给司机排序
        /// </summary>
        /// <param name="ybDT"></param>
        /// <param name="sqlList"></param>
        private void UpdateYbArriverInfo(DataTable ybDT, ref ArrayList sqlList)
        {
            if (null == ybDT || ybDT.Rows.Count < 1)
                return;

            for (int i = 0; i < ybDT.Rows.Count; i++)
            {
                int index = i + 1;
                string sql = "update DriverCombine set dcom_ybarrive = " + index + " where ID = '" + ybDT.Rows[i]["Dcom_ID"].ToString() + "'";

                sqlList.Add(sql);
            }
        }

        private bool refreshSqlList(DataTable roads, DataTable team, ref ArrayList SqlList)
        {
            try
            {
                int count = roads.Rows.Count >= team.Rows.Count ? team.Rows.Count : roads.Rows.Count;   //返回行数少的
                string sql_update = "";
                for (int i = 0; i < count; i++)
                {
                    string roadID = roads.Rows[i]["ID"].ToString();
                    string teamID = team.Rows[i]["DcomID"].ToString();

                    sql_update = "update DriverCombine set dcom_cr_number = '" + roadID + "' where ID = '" + teamID + "'";
                    SqlList.Add(sql_update);
                }
            }
            catch (System.Exception ex)
            {
                log.writeError(ex.ToString());
                return false;
            }
            return true;
        }

        /// <summary>
        /// 返回当前电客车司机组对应班组（早，白，夜，休）
        /// </summary>
        /// <param name="teamID"></param>
        /// <returns></returns>
        public int GetGroupByTeamID(int teamID)
        {
            string sql_getC = "select ctn_cls_id from ClassTurn where ctn_team = " + teamID + " and line = " + line;
            object obj = sqlHelper.GetSingle(sql_getC);

            return obj == null ? -1 : Convert.ToInt32(obj);
        }

        public bool ExangeClass(int team1, int class1, int team2, int class2)
        {
            ArrayList sqlList = new ArrayList();
            string sql_update1 = "update ClassTurn set ctn_cls_id = " + class2 + " where ctn_team = " + team1 + " and line = " + line;
            string sql_update2 = "update ClassTurn set ctn_cls_id = " + class1 + " where ctn_team = " + team2 + " and line = " + line;
            sqlList.Add(sql_update1);
            sqlList.Add(sql_update2);

            return sqlHelper.ExecuteSqlTran(sqlList);
        }

        public string GetVersion()
        {

            string sql_update = "select * from SoftVersion where Code='lb'";
            DataTable dt = sqlHelper.SelectDt(sql_update);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["sVersion"].ToString();
            }
            else
            {
                return "[获取版本失败]";
            }
        }
        #region 月计划所有代码
        /// <summary>
        /// 获取一整个月的时刻表数据
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetDtMonthTimetable(int year, int month)
        {

            //string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql_check = "select TTd.Tableinfo_id,TIF.TableName,TTD.AssignDate" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                " where TIF.LineID = " + line + " and TTD.AssignDate like '" + year.ToString() + "-" + month.ToString().PadLeft(2, '0') + "%'";
            DataTable dt = sqlHelper.SelectDt(sql_check);
            return dt;
        }

        /// <summary>
        /// 获取这个月所用到的时刻表的id
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetDtMonthTimeTableId(int year, int month)
        {
            string sql_check = "select distinct TableInfo_id ,tif.TableName,tif.ID" +
                                " from TableToDate TTD left join TableInfo TIF on TTD.Tableinfo_id = TIF.ID" +
                                " where TIF.LineID = " + line + " and TTD.AssignDate like '" + year.ToString() + "-" + month.ToString().PadLeft(2, '0') + "%'";
            DataTable dt = sqlHelper.SelectDt(sql_check);
            return dt;
        }

        /// <summary>
        /// 根据交路id获取整张交路表
        /// </summary>
        /// <param name="tableID"></param>
        /// <returns></returns>
        public DataTable GetDtCrossingRoadByTableId(string tableID)
        {
            string strsql = "select * from CrossingRoad where tableInfo_id = " + tableID + " and isused = 1 order by number";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        /// <summary>
        /// 获取每组司机的人数
        /// </summary>
        /// <param name="tableID"></param>
        /// <returns></returns>
        public DataTable GetDtDriverCountByTeam()
        {
            string strsql = "select dcom_team, COUNT(*) from DriverCombine where dcom_lineid='" + line + "' Group by dcom_team ";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        /// <summary>
        /// 获取最后的轮班记录
        /// </summary>
        /// <returns></returns>
        public DataTable GetLastClass()
        {
            string strsql = "select top 4 * from ClassTurnPlane where line='" + line + "' order by ctn_date desc ";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        /// <summary>
        /// 获取当前排班顺序
        /// </summary>
        /// <returns></returns>
        public DataTable GetCurrentClass()
        {
            string strsql = "select * from ClassTurn where line='" + line + "'";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        /// <summary>
        /// 获取一张空表来填数据 所以用1>1;
        /// </summary>
        /// <returns></returns>
        public DataTable GetDtClassTrunPlane()
        {
            string strsql = "select  * from ClassTurnPlane where 1>1";
            DataTable dt = sqlHelper.SelectDtAddMissingSchemaAction(strsql);
            return dt;
        }

        public DataTable GetDtDriverCombineCrossingRoadPlane()
        {
            string strsql = "select  * from DriverCombineCrossingRoadPlane where 1>1";
            DataTable dt = sqlHelper.SelectDtAddMissingSchemaAction(strsql);
            return dt;
        }

        /// <summary>
        /// 获取夜班信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetDtLastybInfo()
        {
            string strsql = "select dcdr_driverCombineId,dcdr_driverType,dcdr_team,dcdr_dutyNum,dcdr_crossoffTime,dcom_opdriver,dcom_monidriver from DriverCombineCrossingRoadPlane where dcdr_date= (select max(ctn_date) from ClassTurnPlane) and dcdr_crosszby=3 and line='" + line + "' order by dcdr_crossoffTime";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }


        /// <summary>
        /// 获取司机
        /// </summary>
        /// <returns></returns>
        public DataTable GetDtDriverCombine()
        {

            string strsql = "select ID,dcom_grp_type,dcom_team ,dcom_dutynum ,dcom_opdriver,dcom_monidriver from DriverCombine where dcom_lineid='" + line + "' order by dcom_team, dcom_dutynum";
            DataTable dt = sqlHelper.SelectDt(strsql);
            return dt;
        }

        public void SetDriverCombineCrossingRoadPlane(DataTable dt)
        {
            HelpCom.UpdateSingletable(dt, "DriverCombineCrossingRoadPlane");
        }

        public void SetClassTurnPlane(DataTable dt)
        {
            HelpCom.UpdateSingletable(dt, "ClassTurnPlane");
        }

        public bool AddClassTurnPlaneRecord(int year, int month)
        {
            string sql_add = "insert into ClassTurnPlaneRecord values('" + year.ToString() + "-" + month.ToString().PadLeft(2, '0') + "','" + line + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "')";
            return sqlHelper.ExecuteSql(sql_add) > 0;

        }

        public DataTable GetClassTurnPlaneRecord(int year, int month)
        {
            string strsql = "select * from  ClassTurnPlaneRecord where re_date='" + year.ToString() + "-" + month.ToString().PadLeft(2, '0') + "'and line='" + line + "'";
            return sqlHelper.SelectDt(strsql);
        }

        public DataTable GetClassTurnPlaneRecord()
        {
            string strsql = "select re_date from  ClassTurnPlaneRecord where line='" + line + "'";
            return sqlHelper.SelectDt(strsql);
        }

        public DataTable GetDriverCombineCrossingRoadPlane(int year, int month)
        {

            string strsql = "declare @sql varchar(8000) select @sql = isnull(@sql + '],[' , '') +  CONVERT(varchar(100), dcdr_date, 23) from DriverCombineCrossingRoadPlane where datepart(month,dcdr_date)=" + month + " and datepart(YEAR,dcdr_date)=" + year + " group by dcdr_date order by dcdr_date set @sql = '[' + @sql + ']' declare @sql1 varchar(8000) exec ('SELECT * FROM (SELECT dcdr_team,dcdr_driverCombineId,dcdr_userName ,dcdr_remark,dcdr_date' +' FROM DriverCombineCrossingRoadPlane WHERE line=" + line + "' + ') AS DCRP PIVOT (MAX(dcdr_remark) FOR dcdr_date IN (' + @sql + '))TBL')";
            return sqlHelper.SelectDt(strsql);
        }
        public DataTable GetDtDriverInfo()
        {
            string strSql = "select user_id, user_name,user_view_name from [system_user] where user_dep_id =7 and user_line = " + line + " order by user_view_name";
            DataTable driverDT = sqlHelper.SelectDt(strSql);
            return driverDT;
        }

        /// <summary>
        /// 把计划表里面的轮班计划跟新到今天的轮班记录里面
        /// </summary>
        /// <returns></returns>
        public bool UpdateClassPlane()
        {
            bool resoult;
            try
            {
                string str = "  update ClassTurn set ctn_cls_id=ClassTurnPlane.ctn_cls_id from ClassTurnPlane where ClassTurnPlane.ctn_date='" + DateTime.Now.ToShortDateString() + "' and ClassTurn.ctn_team=ClassTurnPlane.ctn_team and ClassTurn.line=" + line + " and ClassTurnPlane.line=" + line + "";
                ArrayList sqlList = new ArrayList();
                sqlList.Add(str);
                resoult = sqlHelper.ExecuteSqlTran(sqlList);
                if (resoult)
                {
                    log.writeInfo("已经把计划表里面的轮班计划跟新到今天的轮班记录里");
                }
                return resoult;
            }
            catch (Exception ex)
            {
                log.writeError(ex.ToString());
                return false;
            }

        }

        public void UpdateDriverInfo()
        {
            try
            {
                string clearInfo = "update DriverGroup set dgrp_dcom_id=NULL, dgrp_srd_id=NULL,dgrp_attendtime=NULL,dgrp_outtime=NULL,dgrp_room=NULL,replaceDriver=NULL,dgrp_pbyInfo=NULL,dgrp_dtq=NUll" + " where dgrp_line='" + line + "'";
                sqlHelper.ExecuteSql(clearInfo);

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string updateDate = "update DriverGroup set dgrp_date='" + today + "'" + " where dgrp_line='" + line + "'";
                sqlHelper.ExecuteSql(updateDate);

                string updateId = "update DriverGroup set dgrp_dcom_id = NULL where dgrp_line='" + line + "';update DriverGroup set dgrp_dcom_id=DriverCombine.ID from DriverCombine " +
                        "where (DriverCombine.dcom_opdriver=DriverGroup.dgrp_user_id or DriverCombine.dcom_monidriver=DriverGroup.dgrp_user_id or DriverCombine.dcom_monidriverTwo=DriverGroup.dgrp_user_id or DriverCombine.dcom_monidriverThree=DriverGroup.dgrp_user_id)"
                        + " and dcom_date='" + today + "' and dgrp_line='" + line + "'";


                string updateAttendTime = "update drivergroup set attenTime=(select cr.attentime from CrossingRoad as cr left join DriverCombine as dc on dc.dcom_cr_number=cr.id where dgrp_dcom_id=dc.id) "+
                    " where dgrp_date='" + today + "' and dgrp_line='" + line + "'";



                if (sqlHelper.ExecuteSql(updateId) > 0)
                {
                    log.writeInfo("已经更改DriverGroup的司机信息");
                }


                sqlHelper.ExecuteSql("exec pro_update_plane @today ='" + today + "'");
                //string updateSwitch = "update DriverGroup set replaceDriver=s.sr_changer from SwitchedRecord s where s.sr_appdate='" + today + 
                //    "' and s.sr_applicant=DriverGroup.dgrp_user_id;update DriverGroup set replaceDriver=s.sr_applicant from SwitchedRecord s "+
                //    " where s.sr_chgdate ='" + today + "' and s.sr_changer=DriverGroup.dgrp_user_id";
                //if (sqlHelper.ExecuteSql(updateSwitch) > 0)
                //{
                //    log.writeInfo("调班人员更新");
                //}

                if (sqlHelper.ExecuteSql(updateAttendTime) > 0)
                {
                    log.writeInfo("已经更改DriverGroup的计划出勤时间信息");
                }

            }
            catch (Exception e)
            {
                log.writeError(e.ToString());
            }

        }

        #endregion
    }
}
